package com.agileboot.domain.airport.dashboard.db;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 首页统计 Mapper 接口
 */
public interface SirStatisticsMapper extends BaseMapper<Object> {

    /**
     * 按月份统计年度检查数量
     * @param year 年份
     * @return 月份和对应的数量列表
     */
    @Select("SELECT MONTH(start_time) AS month, COUNT(inspection_check_id) AS count " +
            "FROM sir_inspection_check " +
            "WHERE YEAR(start_time) = #{year} " +
            "GROUP BY MONTH(start_time)")
    List<Map<String, Object>> getYearlyInspectionCounts(@Param("year") int year);

    /**
     * 按月份统计年度整改问题数量
     * @param year 年份
     * @return 月份和对应的数量列表
     */
    @Select("SELECT MONTH(check_date) AS month, COUNT(reform_id) AS count " +
            "FROM sir_reform_main " +
            "WHERE YEAR(check_date) = #{year} " +
            "GROUP BY MONTH(check_date)")
    List<Map<String, Object>> getYearlyReformCounts(@Param("year") int year);

    /**
     * 按月份统计年度已完成整改数量
     * @param year 年份
     * @return 月份和对应的数量列表
     */
    @Select("SELECT MONTH(check_date) AS month, COUNT(reform_id) AS count " +
            "FROM sir_reform_main " +
            "WHERE YEAR(check_date) = #{year} AND reform_status = '4' " +
            "GROUP BY MONTH(check_date)")
    List<Map<String, Object>> getYearlyCompletedReformCounts(@Param("year") int year);

    /**
     * 按月份统计年度已检查（适用）的数量
     * @param year 年份
     * @return 月份和对应的数量列表
     */
    @Select("SELECT MONTH(start_time) AS month, COUNT(inspection_check_id) AS count " +
            "FROM sir_inspection_check " +
            "WHERE YEAR(start_time) = #{year} AND inspection_result IN ('1', '2', '3') " +
            "GROUP BY MONTH(start_time)")
    List<Map<String, Object>> getYearlyApplicableCheckCounts(@Param("year") int year);

    /**
     * 按月份统计年度不符合的检查数量
     * @param year 年份
     * @return 月份和对应的数量列表
     */
    @Select("SELECT MONTH(start_time) AS month, COUNT(inspection_check_id) AS count " +
            "FROM sir_inspection_check " +
            "WHERE YEAR(start_time) = #{year} AND inspection_result = '2' " +
            "GROUP BY MONTH(start_time)")
    List<Map<String, Object>> getYearlyNonCompliantCheckCounts(@Param("year") int year);

    /**
     * 获取指定日期范围内的每日检查统计
     * @param startDate 开始日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @param endDate 结束日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @return 每日统计列表，每项包含日期, 总数, 合格数
     */
    @Select("SELECT " +
            "  DATE(start_time) AS check_date, " +
            "  COUNT(inspection_check_id) AS total_checks, " +
            "  SUM(CASE WHEN inspection_result = '1' THEN 1 ELSE 0 END) AS compliant_checks " +
            "FROM sir_inspection_check " +
            "WHERE start_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY check_date " +
            "ORDER BY check_date")
    List<Map<String, Object>> getDailyCheckStats(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 获取指定日期范围内的每日合格率统计所需数据
     * @param startDate 开始日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @param endDate 结束日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @return 每日统计列表，每项包含日期, 适用总数(结果为1,2,3), 合格数(结果为1)
     */
    @Select("SELECT " +
            "  DATE(start_time) AS check_date, " +
            "  SUM(CASE WHEN inspection_result IN ('1', '2', '3') THEN 1 ELSE 0 END) AS applicable_checks, " +
            "  SUM(CASE WHEN inspection_result = '1' THEN 1 ELSE 0 END) AS compliant_checks " +
            "FROM sir_inspection_check " +
            "WHERE start_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY check_date " +
            "ORDER BY check_date")
    List<Map<String, Object>> getDailyComplianceStats(@Param("startDate") String startDate, @Param("endDate") String endDate);


    /**
     * 获取指定日期范围内的每日检查统计数据 (总数, 不合格数, 适用总数)
     * @param startDate 开始日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @param endDate 结束日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @return 每日统计列表
     */
    @Select("SELECT " +
            "  DATE(start_time) AS stat_date, " +
            "  COUNT(inspection_check_id) AS total_checks, " +
            "  SUM(CASE WHEN inspection_result = '2' THEN 1 ELSE 0 END) AS non_compliant_checks, " +
            "  SUM(CASE WHEN inspection_result IN ('1', '2', '3') THEN 1 ELSE 0 END) AS applicable_checks " +
            "FROM sir_inspection_check " +
            "WHERE start_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY stat_date")
    List<Map<String, Object>> getDailyCheckSummary(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 获取指定日期范围内的每日新增安全信息数量
     * @param startDate 开始日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @param endDate 结束日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @return 每日统计列表
     */
    @Select("SELECT " +
            "  DATE(create_time) AS stat_date, " +
            "  COUNT(report_id) AS report_count " +
            "FROM sir_report " +
            "WHERE create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY stat_date")
    List<Map<String, Object>> getDailyReportSummary(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 获取指定日期范围内的每日整改统计数据 (新增数, 完成数)
     * @param startDate 开始日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @param endDate 结束日期 (格式: 'YYYY-MM-DD HH:MM:SS')
     * @return 每日统计列表
     */
    @Select("SELECT " +
            "  DATE(create_time) AS stat_date, " +
            "  COUNT(reform_id) AS total_reforms, " +
            "  SUM(CASE WHEN reform_status = '4' THEN 1 ELSE 0 END) AS completed_reforms " +
            "FROM sir_reform_main " +
            "WHERE create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY stat_date")
    List<Map<String, Object>> getDailyReformSummary(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 查询最近的待办活动（安全信息和整改信息）
     * @return 最近20条活动的列表
     */
    @Select("SELECT " +
            "  t.create_date AS date, " +
            "  d.dept_name AS deptName, " +
            "  t.title, " +
            "  t.source " +
            "FROM " +
            "  ( " +
            "    SELECT " +
            "      DATE_FORMAT(create_time, '%Y-%m-%d') AS create_date, " +
            "      create_time, " +
            "      duty_dept_id, " +
            "      title, " +
            "      '安全信息' AS source " +
            "    FROM " +
            "      sir_report " +
            "    WHERE " +
            "      status = '0' " +
            "    UNION ALL " +
            "    SELECT " +
            "      DATE_FORMAT(create_time, '%Y-%m-%d') AS create_date, " +
            "      create_time, " +
            "      duty_dept_id, " +
            "      question_title AS title, " +
            "      '整改信息' AS source " +
            "    FROM " +
            "      sir_reform_main " +
            "    WHERE " +
            "      reform_status = '5' " +
            "  ) AS t " +
            "LEFT JOIN " +
            "  sys_dept d ON t.duty_dept_id = d.dept_id " +
            "ORDER BY " +
            "  t.create_time DESC " +
            "LIMIT 20")
    List<SirRecentActivityDTO> getRecentActivities();
}